/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package projectmangement;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author trungquy
 */
public class Project_Dao extends DAO{    
    public Project_Dao(){
        init();
    }
    public ArrayList<Project> selectAll(){
        String sql = "SELECT * FROM project";
        ResultSet rs = this.query(sql);
        return resultSetToArray(rs);
    }
    public ArrayList<Project> resultSetToArray(ResultSet rs){
        ArrayList<Project> projects = new ArrayList<Project>();
        try {
            while(rs.next()){
                Project project = new Project();
                project.set_id(rs.getInt("id"));
                project.set_name(rs.getString("name"));
                project.set_start_date(rs.getDate("start_date"));
                project.set_end_date(rs.getDate("end_date"));
                project.set_left(rs.getInt("lft"));
                project.set_right(rs.getInt("rgt"));
                projects.add(project);
            }
        } catch (SQLException ex) {
            System.out.println("Database errors:"+ex.getMessage());
        }
        return projects;
    }

    ArrayList<Project> selectAllDirectChilds(Project project) {
        String sql = "SELECT child.*, (COUNT(parent.id) - 1) AS depth "
                + "FROM project as child, project as parent "
                + "WHERE "
                + "     child.lft >= parent.lft AND child.lft <= parent.rgt "                
                + "GROUP BY child.id "                
                + "HAVING "
                + "     depth = '"+(project.get_depth()+1)+"' "
                + "     AND child.lft >= '"+project.get_left()+"' AND child.lft <= '"+project.get_right()+"' " 
                + "ORDER BY child.lft ASC";
        ResultSet rs = this.query(sql);
        System.out.println(sql);
        return resultSetToArray(rs);
    }
    
    ArrayList<Project> selectAllChilds(Project project){
        String sql = "SELECT child.* FROM project as child, project as parent "
                + "WHERE "
                + "     child.lft > parent.lft AND child.lft < parent.rgt "
                + "     AND parent.id='"+project.get_id()+"' "
                + "GROUP BY child.id "
                + "ORDER BY child.lft ASC";
        ResultSet rs = this.query(sql);
        //System.out.println(sql);
        return resultSetToArray(rs);
    }

    public int getDepthOfNode(Project project) {
        String query = "SELECT child.id, (COUNT(parent.id) - 1) AS depth "
                            +"FROM  project AS child, "
                            +       "project AS parent "                
                            +"WHERE "
                            +       "child.lft >= parent.lft AND child.lft <= parent.rgt "                        
                            +"GROUP BY child.id "
                            +"HAVING child.id = '"+project.get_id()+"' "
                            +"ORDER BY child.lft";                        
        System.out.println(query);
        ResultSet rs = this.query(query);
        try {
            if(rs.next()){
                return rs.getInt("depth");
            }
        } catch (SQLException ex) {
            System.out.println("Database errors: "+ex.getMessage());
        }
        return -1;
    }
    
    public ArrayList<Project> selectRootNodes(){
        String sql = "SELECT child.*, (COUNT(parent.id) - 1) AS depth "
                + "FROM project as child, project as parent "
                + "WHERE "
                + "     child.lft >= parent.lft AND child.lft <= parent.rgt "
                + "GROUP BY child.id "
                + "HAVING depth = 0 "
                + "ORDER BY child.lft ASC";
        System.out.println(sql);
        ResultSet rs = this.query(sql);
        return resultSetToArray(rs);
    }
}
